using SPI.AgentServer.Services.Execution;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 查询在校外来车辆工具 - 查询当前在校的访客车辆
/// </summary>
public class GetVisitorVehiclesTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetVisitorVehiclesTool> _logger;

    public string Name => "get_visitor_vehicles";
    public string Description => "查询当前在校的外来访客车辆信息，包括车牌号、车主、入校时间、在校时长等，支持按时间范围筛选";

    public GetVisitorVehiclesTool(
        IDatabaseExecutor executor,
        ILogger<GetVisitorVehiclesTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (!ValidateParameters(parameters, out var errors))
        {
            return new ToolResult
            {
                Success = false,
                Errors = errors
            };
        }

        try
        {
            // 获取时间范围参数
            string? startTime = null;
            string? endTime = null;

            if (parameters.ContainsKey("startTime"))
            {
                startTime = parameters["startTime"]?.ToString();
            }

            if (parameters.ContainsKey("endTime"))
            {
                endTime = parameters["endTime"]?.ToString();
            }

            // 处理日期格式：如果只有日期没有时间，自动补充
            if (!string.IsNullOrEmpty(startTime))
            {
                startTime = NormalizeDateTime(startTime, isStart: true);
            }

            if (!string.IsNullOrEmpty(endTime))
            {
                endTime = NormalizeDateTime(endTime, isStart: false);
            }

            var sql = @"
SELECT
    ID,
    PlateNumber AS 车牌号,
    Owner AS 车主,
    VehicleType AS 车辆类别,
    PhoneNumber AS 手机号,
    InTime AS 入校时间,
    InEntrance AS 入校地点,
    TIMESTAMPDIFF(HOUR, InTime, NOW()) AS 在校时长_小时,
    Remark AS 备注
FROM TblMotorVehicleManagement
WHERE
    OutTime IS NULL
    AND OutEntrance IS NULL
    AND Temp1 = 2";

            // 添加时间范围筛选
            if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
            {
                sql += $"\n    AND (InTime >= '{startTime}' AND InTime <= '{endTime}')";
            }
            else if (!string.IsNullOrEmpty(startTime))
            {
                sql += $"\n    AND InTime >= '{startTime}'";
            }
            else if (!string.IsNullOrEmpty(endTime))
            {
                sql += $"\n    AND InTime <= '{endTime}'";
            }

            sql += "\nORDER BY InTime DESC";

            _logger.LogInformation("Executing visitor vehicles query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "在校外来车辆查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing visitor vehicles query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询错误: {ex.Message}" }
            };
        }
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        // 所有参数都是可选的
        return true;
    }

    /// <summary>
    /// 规范化日期时间字符串
    /// 如果只有日期，自动补充时间：开始时间补充 00:00:00，结束时间补充 23:59:59 或加一天的 00:00:00
    /// </summary>
    private string NormalizeDateTime(string dateTimeStr, bool isStart)
    {
        dateTimeStr = dateTimeStr.Trim();

        // 如果已经包含时间部分（包含冒号），直接返回
        if (dateTimeStr.Contains(":"))
        {
            return dateTimeStr;
        }

        // 只有日期，补充时间
        if (isStart)
        {
            // 开始时间：补充 00:00:00
            return $"{dateTimeStr} 00:00:00";
        }
        else
        {
            // 结束时间：如果和开始时间是同一天，加一天变成第二天 00:00:00
            // 例如：2025-01-14 -> 2025-01-15 00:00:00
            if (DateTime.TryParse(dateTimeStr, out var date))
            {
                var nextDay = date.AddDays(1);
                return nextDay.ToString("yyyy-MM-dd HH:mm:ss");
            }

            // 解析失败，返回原值加 23:59:59
            return $"{dateTimeStr} 23:59:59";
        }
    }
}
